﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace Jurassic_Island
{
    public class CADLugares
    {
        static private String cadenaConexion = ConfigurationManager.ConnectionStrings["JurassicBDConnectionString"].ConnectionString;

        public DataSet ObtenerTablaLugares()
        {
            DataSet dsLugares = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select nombre_lug as 'Nombre' ,coordx_lug as 'Coordenada X' ,coordy_lug as 'Coordenada Y' ,descripcion_lug as 'Descripcion', imagen_lug as 'Imagen' from Lugares";
                SqlDataAdapter daLugares = new SqlDataAdapter(consulta, conexion);
                daLugares.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daLugares.Fill(dsLugares, "Lugares");
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsLugares;
        }

        public DataSet ObtenerFitradoLugares(string filtro)
        {
            DataSet dsLugares = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select nombre_lug as 'Nombre' ,coordx_lug as 'Coordenada X' ,coordy_lug as 'Coordenada Y' ,descripcion_lug as 'Descripcion', imagen_lug as 'Imagen' from Lugares where nombre_lug like '%" + filtro + "%'";
                SqlDataAdapter daLugares = new SqlDataAdapter(consulta, conexion);
                daLugares.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daLugares.Fill(dsLugares, "Lugares");
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsLugares;
        }

       
        public bool ActualizarLugares(DataSet dsLugares)
        {
            bool resultado = false;

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select nombre_lug as 'Nombre', coordx_lug as 'Coordenada X', coordy_lug as 'Coordenada Y', descripcion_lug as 'Descripcion', imagen_lug as 'Imagen' from Lugares";
                SqlDataAdapter daLugar = new SqlDataAdapter(consulta, conexion);
                SqlCommandBuilder cb = new SqlCommandBuilder(daLugar);
                cb.ConflictOption = ConflictOption.OverwriteChanges;
                daLugar.UpdateCommand = cb.GetUpdateCommand();
                daLugar.Update(dsLugares, "Lugares");
                dsLugares.Tables["Lugares"].AcceptChanges();

                resultado = true;
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return resultado;
        }

        public bool ExisteLugar(string nombre)
        {
            bool existe = false;
            DataSet dsLugar = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "select nombre_lug as 'Nombre' from Lugares where nombre_lug LIKE '" + nombre + "'";
                SqlDataAdapter daObjeto = new SqlDataAdapter(consulta, conexion);
                daObjeto.Fill(dsLugar, "Lugares");

                if (dsLugar.Tables["Lugares"].Rows.Count > 0)
                    existe = true;
            }

            catch (Exception ex)
            {
                throw (ex);
            }

            return existe;
        }

        public DataSet ObtenerNombresLugares()
        {
            DataSet dsLugares = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select nombre_lug as 'Nombre' from Lugares";
                SqlDataAdapter daLugares = new SqlDataAdapter(consulta, conexion);
                daLugares.Fill(dsLugares, "Lugares");
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsLugares;
        }
    }
}
